IF OBJECT_ID('dbo.GetPrincipalInvestigators') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetPrincipalInvestigators
    IF OBJECT_ID('dbo.GetPrincipalInvestigators') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetPrincipalInvestigators >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetPrincipalInvestigators >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetPrincipalInvestigators
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 5/24/2007
--
-- Description: Returns list of Principal Investigators from Insight
--
-- Parameters:  @Search, wildcard search in "LastName, FirstName"
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetPrincipalInvestigators()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 7/11/07     KC        Created
-- 8/6/07      KC        Dropped View from Search for Performance
-- 8/15/07     KC        Added Security and changed User Permissions
--                       Used Institution to limit result
-- 8/16/07     KC        Commented out Security for performance while testing
-- 8/17/07     KC        Security changed to Cached table
-- 8/23/07     KC        Added Search name wildcard
-- 8/24/07     KC        Made search on last name only
-- 11/1/07     KC        Added Institution to search.
--                       Changed source to Agreement_info table!
-- 12/11/08   CB         Added column for infoed searches, and ability to recive either Insight institution Id or 
--                       institution name (due to being used for Infoed reports as well)
-- 12/17/08    KC        changed security to new function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
-- 01/07/08    KC        Roll back temp table change.
--
-- =======================================================================
CREATE PROCEDURE dbo.GetPrincipalInvestigators
     (@Name                        VARCHAR(50)=NULL,
     @Institution                  VARCHAR(200)=NULL,
     @DomainUserId                 INT
     )
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     PIId                INT
     PIName              VARCHAR             
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT,
               @InstitutionId    int
               
    --This is to handle that for Infoed you need to bind to Institution
     IF ISNUMERIC(@Institution) =0
     BEGIN
           SELECT @InstitutionId = Id 
           FROM FwkDomainOrganization 
           WHERE Institution =@Institution AND FwkDomainOrganizationTypeId =2
     END
     ELSE
     BEGIN
       SET @InstitutionId = CONVERT(int,@Institution)
     END               

     -- Result Query   
     SELECT DISTINCT AI.PrincipalInvestigatorId AS PIId,
               AI.PrincipalInvestigator as PIName,
               RTRIM(P.LastName) + RTRIM(P.FirstName) + RTRIM(IsNull(' ' + P.MiddleName,'')) AS PIInfoEdSearchName
     FROM      dbo.AgrAgreement_Info AI 
               LEFT OUTER JOIN admPerson p ON AI.PrincipalInvestigatorId  = p.id 
               -- Security Issues: Not all Users can see all Agreements!
               INNER JOIN dbo.fnGetSecurityAgreements(@DomainUserId) AUTH ON AI.AgreementId = AUTH.AgreementId
     WHERE     AI.PrincipalInvestigatorId IS NOT NULL AND
               AI.PrincipalInvestigator IS NOT NULL AND RTRIM(AI.PrincipalInvestigator) != '' AND
               ((RTRIM(AI.PrincipalInvestigator) LIKE @Name + '%') OR @Name IS NULL) AND
               (@Institution IS NULL OR AI.InstitutionId = @InstitutionId)
     ORDER BY AI.PrincipalInvestigator               
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetPrincipalInvestigators') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetPrincipalInvestigators >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetPrincipalInvestigators >>>'
go

GRANT EXECUTE ON dbo.GetPrincipalInvestigators TO InsightReport,Insight
go

